﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spDBA_update_server]
#-- Purpose:		Updates the server record with the latest information
#--	Last Update:	02/21/2012
#--					For a complete history - please review comments in Version
#--					Control.
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spDBA_update_server]
(
	@server_name		varchar(60),
	@engine_edition		int,
	@product_version	varchar(25),
	@product_level		varchar(5),
	@has_master_jobs	bit,
	@has_local_jobs		bit,
	@backup_directory	varchar(255),
	@integrated_only	bit				= 0,
	@edition_id			int				= -1534726760,
	@sql_service		varchar(255)	= NULL,
	@agent_service		varchar(255)	= NULL
)
AS

SET NOCOUNT ON

IF NOT EXISTS (SELECT TOP 1 * FROM dbo.tblServer WHERE srv_server_name = @server_name)
	INSERT INTO dbo.tblServer (
		srv_server_name, srv_engine_edition, srv_edition_id, srv_product_version, 
		srv_product_level, srv_has_master_jobs, srv_has_local_jobs, srv_backup_directory, 
		srv_is_mixed_mode, srv_sql_service, srv_agent_service
	)
	VALUES (
		@server_name, @engine_edition, @edition_id, @product_version, 
		@product_level, @has_master_jobs, @has_local_jobs, @backup_directory, 
		(@integrated_only ^ 1), @sql_service, @agent_service
	)
ELSE IF NOT EXISTS (SELECT	TOP 1 * 
					FROM	dbo.tblServer 
					WHERE	srv_server_name = @server_name
							AND srv_engine_edition = @engine_edition
							AND ISNULL(srv_edition_id, -9) = @edition_id
							AND srv_product_version = @product_version
							AND srv_product_level = @product_level
							AND srv_has_master_jobs = @has_master_jobs
							AND srv_has_local_jobs = @has_local_jobs
							AND ISNULL(srv_backup_directory, '') = @backup_directory
							AND srv_is_mixed_mode = (@integrated_only ^ 1)
							AND ISNULL(srv_sql_service, '') = ISNULL(@sql_service, '')
							AND ISNULL(srv_agent_service, '') = ISNULL(@agent_service, '')
					)
	UPDATE	dbo.tblServer
	SET		srv_engine_edition = @engine_edition,
			srv_edition_id = @edition_id,
			srv_product_version = @product_version,
			srv_product_level = @product_level,
			srv_has_master_jobs = @has_master_jobs,
			srv_has_local_jobs = @has_local_jobs,
			srv_backup_directory = (CASE WHEN @backup_directory <> '' THEN @backup_directory ELSE NULL END),
			srv_is_mixed_mode = (@integrated_only ^ 1),
			srv_last_update = GETDATE(),
			srv_sql_service = @sql_service,
			srv_agent_service = @agent_service
	WHERE	srv_server_name = @server_name

SET NOCOUNT OFF
